At this point, you should have a better idea of the common functionality found among all .NET data providers. Recall that even though the exact names of the implementing types will differ among data providers, you can program against these types in a similar manner—that’s the beauty of interfacebased polymorphism. For example, if you define a method that takes an IDbConnection parameter, you can pass in any ADO.NET connection object:
public static void OpenConnection(IDbConnection cn) { // Open the incoming connection for the caller. cn.Open(); }
Note Interfaces are not strictly required; you can achieve the same level of abstraction using abstract base classes (such as DbConnection) as parameters or return values.
The same holds true for member return values. For example, consider the following simple C# Console Application project (named MyConnectionFactory), which allows you to obtain a specific connection object based on the value of a custom enumeration. For diagnostic purposes, you simply print out the underlying connection object using reflection services, and then enter the following code:
using System; using System.Collections.Generic; using System.Linq; using System.Text; // Need these to get definitions of common interfaces, // and various connection objects for our test. using System.Data; using System.Data.SqlClient; using System.Data.Odbc; using System.Data.OleDb; namespace MyConnectionFactory { // A list of possible providers. enum DataProvider { SqlServer, OleDb, Odbc, None } class Program { static void Main(string[] args) { Console.WriteLine("**** Very Simple Connection Factory *****\n"); // Get a specific connection. IDbConnection myCn = GetConnection(DataProvider.SqlServer); Console.WriteLine("Your connection is a {0}", myCn.GetType().Name); // Open, use and close connection... Console.ReadLine(); } // This method returns a specific connection object // based on the value of a DataProvider enum. static IDbConnection GetConnection(DataProvider dp) { IDbConnection conn = null; switch (dp) { case DataProvider.SqlServer: conn = new SqlConnection(); break; case DataProvider.OleDb: conn = new OleDbConnection(); break; case DataProvider.Odbc: conn = new OdbcConnection(); break; } return conn; } } }
The benefit of working with the general interfaces of System.Data (or for that matter, the abstract base classes of System.Data.Common) is that you have a much better chance of building a flexible code base that can evolve over time. For example, today you might be building an application that targets Microsoft SQL Server; however, it’s possible your company could switch to Oracle months down the road. If you build a solution that hard-codes the MS SQL Server–specific types of System.Data.SqlClient, you would obviously need to edit, recompile, and redeploy the assembly should the back-end database management system change.
To increase the flexibility of your ADO.NET applications, you could incorporate a client-side *.config file that uses custom key/value pairs within the <appSettings> element. Recall from Chapter 14 that you can obtain the custom data stored within a *.config file programmatically by using types within the System.Configuration namespace. For example, assume you have specified a data provider value within a configuration file, as in this example:
<configuration> <appSettings> <!-- This key value maps to one of our enum values--> <add key="provider" value="SqlServer"/> </appSettings> </configuration>
With this, you could update Main() to obtain the underlying data provider programmatically. Doing this essentially builds a connection object factory that allows you to change the provider, but without requiring you to recompile your code base (you simply change the *.config file). Here are the relevant updates to Main():
static void Main(string[] args) { Console.WriteLine("**** Very Simple Connection Factory *****\n"); // Read the provider key. string dataProvString = ConfigurationManager.AppSettings["provider"]; // Transform string to enum. DataProvider dp = DataProvider.None; if(Enum.IsDefined(typeof(DataProvider), dataProvString)) dp = (DataProvider)Enum.Parse(typeof(DataProvider), dataProvString); else Console.WriteLine("Sorry, no provider exists!"); // Get a specific connection. IDbConnection myCn = GetConnection(dp); if(myCn != null) Console.WriteLine("Your connection is a {0}", myCn.GetType().Name); // Open, use, and close connection... Console.ReadLine(); }
Note To use the ConfigurationManager type, be sure to set a reference to the System.Configuration.dll assembly and import the System.Configuration namespace.
At this point, you have authored some ADO.NET code that allows you to specify the underlying connection dynamically. One obvious problem, however, is that this abstraction is only used within the MyConnectionFactory.exe application. If you were to rework this example within a .NET code library (e.g., MyConnectionFactory.dll), you would be able to build any number of clients that could obtain various connection objects using layers of abstraction.
However, obtaining a connection object is only one aspect of working with ADO.NET. To make a worthwhile data provider factory library, you would also have to account for command objects, data readers, data adapters, transaction objects, and other data-centric types. Building such a code library would not necessarily be difficult, but it would require a considerable amount of code and time.
Since the release of .NET 2.0, the kind folks in Redmond have built this exact functionality directly into the .NET base class libraries. You will examine this formal API in just a moment; however, first you need to create a custom database to use throughout this chapter (and for many chapters to come).
Source Code You can find the MyConnectionFactory project under the Chapter 21 subdirectory.